﻿using System;
using System.Data;
using System.Data.Common;
using Foothill.Common.Logging;
using Foothill.DataAccess.Interface;
using Foothill.Model;

namespace Foothill.DataAccess.Repository
{
    public class UserAccountRepo : DbAccessBase, IUserAccountRepo
    {
        public int GetUserIdByEmailAddress(string email)
        {
            if (string.IsNullOrWhiteSpace(email))
            {
                return -1;
            }

            string sql = "SELECT [UserAccountId] FROM [UserAccount] WHERE Email=@email";
            DbCommand dbCommand = FoothillDB.GetSqlStringCommand(sql);
            FoothillDB.AddInParameter(dbCommand, "email", DbType.String, email);

            int acctId = -1;
            try
            {
                acctId = GetDbInt32(FoothillDB.ExecuteScalar(dbCommand), -1);
            }
            catch (Exception exception)
            {
                Log.Error(FoothillModule.DataAccess, exception.Message, exception);
                throw new DataAccessException(string.Format("Error when getting account Id based on email address ={0}", email), exception);
            }

            return acctId;
        }

        public int GetUserIdByUsername(string userName)
        {
            if (string.IsNullOrWhiteSpace(userName))
            {
                return -1;
            }

            string sql = "SELECT [UserAccountId] FROM [UserAccount] WHERE Username=@Username";
            DbCommand dbCommand = FoothillDB.GetSqlStringCommand(sql);
            FoothillDB.AddInParameter(dbCommand, "Username", DbType.String, userName);

            int acctId = -1;
            try
            {
                acctId = GetDbInt32(FoothillDB.ExecuteScalar(dbCommand), -1);
            }
            catch (Exception exception)
            {
                Log.Error(FoothillModule.DataAccess, exception.Message, exception);
                throw new DataAccessException(string.Format("Error when getting account Id based on userName:{0}", userName), exception);
            }

            return acctId;
        }

        public int CreateNewUserAccount(UserAccountDto newAcctInfo)
        {
            DbCommand dbCommand = FoothillDB.GetStoredProcCommand("CreateNewUserAccount");

            FoothillDB.AddInParameter(dbCommand, "Email", DbType.String, newAcctInfo.Email);
            FoothillDB.AddInParameter(dbCommand, "Username", DbType.String, newAcctInfo.Username);
            FoothillDB.AddInParameter(dbCommand, "Password", DbType.String, newAcctInfo.Password);
            FoothillDB.AddInParameter(dbCommand, "AccountStateId", DbType.Int32, newAcctInfo.AccountStateId);
            FoothillDB.AddInParameter(dbCommand, "ActivationCode", DbType.Guid, newAcctInfo.ActivationCode);
            FoothillDB.AddInParameter(dbCommand, "ActivationEmailSentDateTime", DbType.DateTime, newAcctInfo.ActivationEmailSentDateTime);
            FoothillDB.AddOutParameter(dbCommand, "@NewAccountId", DbType.Int32, 4);

            int newAccountId = -1;
            try
            {
                FoothillDB.ExecuteNonQuery(dbCommand);
                newAccountId = GetDbInt32(FoothillDB.GetParameterValue(dbCommand, "NewAccountId"), -1);
            }
            catch (Exception exception)
            {
                Log.Error(FoothillModule.DataAccess, exception.Message, exception);
                throw new DataAccessException("Error when inserting a new UserAccount to database", exception);
            }

            return newAccountId;
        }

        public int GetUserIdByEmailAndPassword(string email, string hashedPassword)
        {
            if (string.IsNullOrWhiteSpace(email) || string.IsNullOrWhiteSpace(hashedPassword))
            {
                return -1;
            }

            string sql = "SELECT [UserAccountId] FROM [UserAccount] WHERE Email=@email AND Password=@password";
            DbCommand dbCommand = FoothillDB.GetSqlStringCommand(sql);

            FoothillDB.AddInParameter(dbCommand, "email", DbType.String, email);
            FoothillDB.AddInParameter(dbCommand, "password", DbType.String, hashedPassword);

            int acctId = -1;
            try
            {
                acctId = GetDbInt32(FoothillDB.ExecuteScalar(dbCommand), -1);
            }
            catch (Exception exception)
            {
                Log.Error(FoothillModule.DataAccess, exception.Message, exception);
                throw new DataAccessException(string.Format("Error when getting account Id for email {0} address with the password", email), exception);
            }

            return acctId;
        }

        public UserAccountDto GetUserAccountByActivationCode(Guid authCode)
        {
            string sql = "SELECT [UserAccountId],[AccountStateId],[ActivationEmailSentDateTime] FROM [UserAccount] WHERE ActivationCode=@activationCode";

            DbCommand dbCommand = FoothillDB.GetSqlStringCommand(sql);
            FoothillDB.AddInParameter(dbCommand, "activationCode", DbType.Guid, authCode);

            UserAccountDto result = null;

            try
            {
                using (IDataReader reader = FoothillDB.ExecuteReader(dbCommand))
                {
                    if (reader.Read())
                    {
                        result = new UserAccountDto
                        {
                            UserAccountId = GetDbInt32(reader["UserAccountId"], -1),
                            AccountStateId = GetDbInt32(reader["AccountStateId"], -1),
                            ActivationEmailSentDateTime = GetDbSmallDateTime(reader["ActivationEmailSentDateTime"])
                        };
                    }
                }
            }
            catch (Exception exception)
            {
                Log.Error(FoothillModule.DataAccess, exception.Message, exception);
                throw new DataAccessException(string.Format("Error when getting account by activation code {0}", authCode), exception);
            }

            return result;
        }

        public UserAccountDto GetUserAccountByEmail(string email)
        {
            string sql = @"SELECT [UserAccountId],[AccountStateId],[Username],[Password],
               [TempPassword], [ActivationEmailSentDateTime] FROM [UserAccount] WHERE [Email]=@email";

            DbCommand dbCommand = FoothillDB.GetSqlStringCommand(sql);
            FoothillDB.AddInParameter(dbCommand, "email", DbType.String, email);

            UserAccountDto result = null;
            
            try
            {
                using (IDataReader reader = FoothillDB.ExecuteReader(dbCommand))
                {
                    if (reader.Read())
                    {
                        result = new UserAccountDto
                        {
                            UserAccountId = GetDbInt32(reader["UserAccountId"], -1),
                            AccountStateId = GetDbInt32(reader["AccountStateId"], -1),
                            Username = GetDbString(reader["Username"], string.Empty),
                            Password = GetDbString(reader["Password"], string.Empty),
                            Email = email,
                            TempPassword = GetDbString(reader["TempPassword"], string.Empty),
                            ActivationEmailSentDateTime = GetDbSmallDateTime(reader["ActivationEmailSentDateTime"])
                        };
                    }
                }
            }
            catch (Exception exception)
            {
                Log.Error(FoothillModule.DataAccess, exception.Message, exception);
                throw new DataAccessException(string.Format("Error when getting account by email: {0}", email), exception);
            }

            return result;
        }

        // update [AccountStateId] and [DateActivated] fields
        public void ActivateUserAccount(UserAccountDto userAcct)
        {
            string sql = "UPDATE [UserAccount] SET [AccountStateId]=@accountStateId, [DateActivated]=@dateActivated WHERE UserAccountId=@userAccountId";

            DbCommand dbCommand = FoothillDB.GetSqlStringCommand(sql);
            FoothillDB.AddInParameter(dbCommand, "userAccountId", DbType.Int32, userAcct.UserAccountId);
            FoothillDB.AddInParameter(dbCommand, "accountStateId", DbType.Int32, userAcct.AccountStateId);
            FoothillDB.AddInParameter(dbCommand, "dateActivated", DbType.DateTime, userAcct.DateActivated);

            try
            {
                FoothillDB.ExecuteNonQuery(dbCommand);
            }
            catch (Exception exception)
            {
                Log.Error(FoothillModule.DataAccess, exception.Message, exception);
                throw new DataAccessException(string.Format("Error when activating user account, id = {0}", userAcct.UserAccountId), exception);
            }
        }

        public UserAccountDto GetUserAccountByEmailAndPassword(string email, string hashedPassword)
        {
            UserAccountDto user = null;
            if (string.IsNullOrWhiteSpace(email) || string.IsNullOrWhiteSpace(hashedPassword))
            {
                return user;
            }

            string sql = "SELECT [UserAccountId], [Username],[AccountStateId] FROM [UserAccount] WHERE Email=@email AND Password=@password";

            DbCommand dbCommand = FoothillDB.GetSqlStringCommand(sql);

            FoothillDB.AddInParameter(dbCommand, "email", DbType.String, email);
            FoothillDB.AddInParameter(dbCommand, "password", DbType.String, hashedPassword);

            try
            {
                using (IDataReader reader = FoothillDB.ExecuteReader(dbCommand))
                {
                    if (reader.Read())
                    {
                        user = new UserAccountDto
                        {
                            UserAccountId = GetDbInt32(reader["UserAccountId"], -1),
                            Username = GetDbString(reader["Username"], string.Empty),
                            AccountStateId = GetDbInt32(reader["AccountStateId"], -1)
                        };
                    }
                }

                return user;
            }
            catch (Exception exception)
            {
                Log.Error(FoothillModule.DataAccess, exception.Message, exception);
                throw new DataAccessException(string.Format("Error when getting account for email = {0}", email), exception);
            }
        }

        public UserAccountDto GetUserAccountDetailsByUserName(string username)
        {
            UserAccountDto user = null;

            string sql = @"SELECT [UserAccountId],[Email],[Username],[ContactName],[ContactPhone],[AlternativePhone],[AccountStateId],
                [ActivationCode], [ActivationEmailSentDateTime], [DateActivated],[LastLoginDate],
                [UserLogoFilePath], [UserProfilePhotoPath], [Intro] FROM [UserAccount] WHERE [Username]=@username";

            DbCommand dbCommand = FoothillDB.GetSqlStringCommand(sql);
            FoothillDB.AddInParameter(dbCommand, "username", DbType.String, username);

            try
            {
                using (IDataReader reader = FoothillDB.ExecuteReader(dbCommand))
                {
                    if (reader.Read())
                    {
                        user = ConstructUserAccountInstance(reader);
                    }
                }

                return user;
            }
            catch (Exception exception)
            {
                Log.Error(FoothillModule.DataAccess, exception.Message, exception);
                throw new DataAccessException(string.Format("Error when getting detail info for user with username = {0}", username), exception);
            }
        }

        public UserAccountDto GetUserAccountDetailsByUserId(int userId)
        {
            UserAccountDto user = null;

            string sql = @"SELECT [UserAccountId],[Email],[ContactName],[ContactPhone],[Username],[AlternativePhone],[AccountStateId],
                [ActivationCode], [ActivationEmailSentDateTime], [DateActivated],[LastLoginDate],
                [UserLogoFilePath], [UserProfilePhotoPath],[Intro] FROM [UserAccount] WHERE [UserAccountId]=@userId";

            DbCommand dbCommand = FoothillDB.GetSqlStringCommand(sql);
            FoothillDB.AddInParameter(dbCommand, "userId", DbType.Int32, userId);

            try
            {
                using (IDataReader reader = FoothillDB.ExecuteReader(dbCommand))
                {
                    if (reader.Read())
                    {
                        user = ConstructUserAccountInstance(reader);
                    }
                }

                return user;
            }
            catch (Exception exception)
            {
                Log.Error(FoothillModule.DataAccess, exception.Message, exception);
                throw new DataAccessException(string.Format("Error when getting detail info for user with userId = {0}", userId), exception);
            }
        }

        public void SavePost(int userId, int postId)
        {
            DbCommand dbCommand = FoothillDB.GetStoredProcCommand("UserSavePost");

            FoothillDB.AddInParameter(dbCommand, "PostId", DbType.Int32, postId);
            FoothillDB.AddInParameter(dbCommand, "UserId", DbType.Int32, userId);

            try
            {
                FoothillDB.ExecuteNonQuery(dbCommand);
            }
            catch (Exception exception)
            {
                Log.Error(FoothillModule.DataAccess, exception.Message, exception);
                throw new DataAccessException(string.Format("Error when saving post for user, userId = {0}, postId = {1}", userId, postId), exception);
            }
        }

        public void UpdateUserAccountWithTempPassword(int userId, string tmpPassword, int accountStateId)
        {
            string sql = @"UPDATE [UserAccount] SET [AccountStateId]=@accountStateId, 
                [TempPassword]=@tempPassword,TempPasswordEmailSentDateTime=@tempPasswordEmailSentDateTime 
                WHERE UserAccountId=@userAccountId";

            DbCommand dbCommand = FoothillDB.GetSqlStringCommand(sql);
            FoothillDB.AddInParameter(dbCommand, "userAccountId", DbType.Int32, userId);
            FoothillDB.AddInParameter(dbCommand, "accountStateId", DbType.Int32, accountStateId);
            FoothillDB.AddInParameter(dbCommand, "tempPassword", DbType.String, tmpPassword);
            FoothillDB.AddInParameter(dbCommand, "tempPasswordEmailSentDateTime", DbType.DateTime, DateTime.Now);

            try
            {
                FoothillDB.ExecuteNonQuery(dbCommand);
            }
            catch (Exception exception)
            {
                Log.Error(FoothillModule.DataAccess, exception.Message, exception);
                throw new DataAccessException(string.Format("Error when update user account with temp password, id = {0}", userId), exception);
            }
        }

        public void ChangePassword(int userId, string hashedNewPassword)
        {
            string sql = "UPDATE [UserAccount] SET [Password]=@password, [LastPasswordChangedDateTime]=@datetime WHERE UserAccountId=@userId";

            DbCommand dbCommand = FoothillDB.GetSqlStringCommand(sql);

            FoothillDB.AddInParameter(dbCommand, "userId", DbType.Int32, userId);
            FoothillDB.AddInParameter(dbCommand, "password", DbType.String, hashedNewPassword);
            FoothillDB.AddInParameter(dbCommand, "datetime", DbType.DateTime, DateTime.Now);

            try
            {
                FoothillDB.ExecuteNonQuery(dbCommand);
            }
            catch (Exception exception)
            {
                Log.Error(FoothillModule.DataAccess, exception.Message, exception);
                throw new DataAccessException(string.Format("Error when update user account with new password, userId = {0}", userId), exception);
            }
        }

        public void RestorePasswordRecoveryState(int userId, int activeUserStateId)
        {
            string sql = "UPDATE [UserAccount] SET [TempPassword]=NULL, [AccountStateId]=@acctStateId WHERE UserAccountId=@userId";

            DbCommand dbCommand = FoothillDB.GetSqlStringCommand(sql);

            FoothillDB.AddInParameter(dbCommand, "userId", DbType.Int32, userId);
            FoothillDB.AddInParameter(dbCommand, "acctStateId", DbType.Int32, activeUserStateId);

            try
            {
                FoothillDB.ExecuteNonQuery(dbCommand);
            }
            catch (Exception exception)
            {
                Log.Error(FoothillModule.DataAccess, exception.Message, exception);
                throw new DataAccessException(string.Format("Error when restore user account to active state from password recovery state, userId = {0}", userId), exception);
            }
        }

        public void ChangePasswordAfterLoginUsingTempPassword(int userId, int activeUserStateId, string hashedNewPassword)
        {
            string sql = @"UPDATE [UserAccount] SET [TempPassword]=NULL, [AccountStateId]=@acctStateId,
                Password=@password, LastPasswordChangedDateTime=@datetime WHERE UserAccountId=@userId";

            DbCommand dbCommand = FoothillDB.GetSqlStringCommand(sql);

            FoothillDB.AddInParameter(dbCommand, "userId", DbType.Int32, userId);
            FoothillDB.AddInParameter(dbCommand, "acctStateId", DbType.Int32, activeUserStateId);
            FoothillDB.AddInParameter(dbCommand, "password", DbType.String, hashedNewPassword);
            FoothillDB.AddInParameter(dbCommand, "datetime", DbType.DateTime, DateTime.Now);

            try
            {
                FoothillDB.ExecuteNonQuery(dbCommand);
            }
            catch (Exception exception)
            {
                Log.Error(FoothillModule.DataAccess, exception.Message, exception);
                throw new DataAccessException(string.Format("Error when update password after log in using temp password, userId = {0}", userId), exception);
            }
        }

        public void UpdateUserAccount(int userId, UserAccountDto dto)
        {
            string sql = @"UPDATE [UserAccount] SET ContactPhone=@phone, ContactName=@contactName, 
               Intro=@intro WHERE UserAccountId=@userId";

            DbCommand dbCommand = FoothillDB.GetSqlStringCommand(sql);

            FoothillDB.AddInParameter(dbCommand, "userId", DbType.Int32, userId);
            FoothillDB.AddInParameter(dbCommand, "phone", DbType.String, dto.ContactPhone);
            FoothillDB.AddInParameter(dbCommand, "contactName", DbType.String, dto.ContactName);
            FoothillDB.AddInParameter(dbCommand, "intro", DbType.String, dto.Intro);

            try
            {
                FoothillDB.ExecuteNonQuery(dbCommand);
            }
            catch (Exception exception)
            {
                Log.Error(FoothillModule.DataAccess, exception.Message, exception);
                throw new DataAccessException(string.Format("Error when update UserAccount, userId = {0}", userId), exception);
            }
        }

        public void UpdateLogoFilePath(int userId, string logoFilePath)
        {
            string sql = @"UPDATE [UserAccount] SET UserLogoFilePath = @logoFilePath WHERE UserAccountId=@userId";

            DbCommand dbCommand = FoothillDB.GetSqlStringCommand(sql);

            FoothillDB.AddInParameter(dbCommand, "userId", DbType.Int32, userId);
            FoothillDB.AddInParameter(dbCommand, "logoFilePath", DbType.String, logoFilePath);

            try
            {
                FoothillDB.ExecuteNonQuery(dbCommand);
            }
            catch (Exception exception)
            {
                Log.Error(FoothillModule.DataAccess, exception.Message, exception);
                throw new DataAccessException(string.Format("Error when updating user logo file path, userId = {0}", userId), exception);
            }
        }

        public void UpdateProfilePhotoFilePath(int userId, string filePath)
        {
            string sql = @"UPDATE [UserAccount] SET UserProfilePhotoPath = @filePath WHERE UserAccountId=@userId";

            DbCommand dbCommand = FoothillDB.GetSqlStringCommand(sql);

            FoothillDB.AddInParameter(dbCommand, "userId", DbType.Int32, userId);
            FoothillDB.AddInParameter(dbCommand, "filePath", DbType.String, filePath);

            try
            {
                FoothillDB.ExecuteNonQuery(dbCommand);
            }
            catch (Exception exception)
            {
                Log.Error(FoothillModule.DataAccess, exception.Message, exception);
                throw new DataAccessException(string.Format("Error when updating user profile photo path, userId = {0}", userId), exception);
            }
        }

        public void DeleteUserAccount(int userId)
        {
            string sql = @"DELETE FROM [UserAccount] WHERE UserAccountId=@userId";

            DbCommand dbCommand = FoothillDB.GetSqlStringCommand(sql);

            FoothillDB.AddInParameter(dbCommand, "userId", DbType.Int32, userId);

            try
            {
                FoothillDB.ExecuteNonQuery(dbCommand);
            }
            catch (Exception exception)
            {
                Log.Error(FoothillModule.DataAccess, exception.Message, exception);
                throw new DataAccessException(string.Format("Error when deleting user account, userId = {0}", userId), exception);
            }
        }

        public void UpdatePendingAccountAfterSendingActivation(UserAccountDto dto)
        {
            string sql = @"UPDATE [UserAccount] SET 
                Username = @username, Password=@password, AccountStateId=@accountStateId,
                ActivationCode=@activationCode, ActivationEmailSentDateTime=@activationEmailSentDateTime 
                WHERE UserAccountId=@userId";

            DbCommand dbCommand = FoothillDB.GetSqlStringCommand(sql);

            FoothillDB.AddInParameter(dbCommand, "userId", DbType.Int32, dto.UserAccountId);
            FoothillDB.AddInParameter(dbCommand, "username", DbType.String, dto.Username);
            FoothillDB.AddInParameter(dbCommand, "password", DbType.String, dto.Password);
            FoothillDB.AddInParameter(dbCommand, "accountStateId", DbType.Int32, dto.AccountStateId);
            FoothillDB.AddInParameter(dbCommand, "activationCode", DbType.Guid, dto.ActivationCode);
            FoothillDB.AddInParameter(dbCommand, "activationEmailSentDateTime", DbType.DateTime, DateTime.Now);

            try
            {
                FoothillDB.ExecuteNonQuery(dbCommand);
            }
            catch (Exception exception)
            {
                Log.Error(FoothillModule.DataAccess, exception.Message, exception);
                throw new DataAccessException(string.Format("Error when updating pending user account after sending activation, userId = {0}", dto.UserAccountId), exception);
            }
        }

        private UserAccountDto ConstructUserAccountInstance(IDataReader reader)
        {
            UserAccountDto user = new UserAccountDto
            {
                UserAccountId = GetDbInt32(reader["UserAccountId"], -1),
                Username = GetDbString(reader["Username"], string.Empty),
                ContactName = GetDbString(reader["ContactName"], string.Empty),
                Email = GetDbString(reader["Email"], string.Empty),
                ContactPhone = GetDbString(reader["ContactPhone"], string.Empty),
                AlternativePhone = GetDbString(reader["AlternativePhone"], string.Empty),
                AccountStateId = GetDbInt32(reader["AccountStateId"], -1),
                ActivationCode = GetDbGuid(reader["ActivationCode"]),
                ActivationEmailSentDateTime = GetDbSmallDateTime(reader["ActivationEmailSentDateTime"]),
                DateActivated = GetDbSmallDateTime(reader["DateActivated"]),
                LastLoginDate = GetDbSmallDateTime(reader["LastLoginDate"]),
                UserLogoFilePath = GetDbString(reader["UserLogoFilePath"], string.Empty),
                UserProfilePhotoPath = GetDbString(reader["UserProfilePhotoPath"], string.Empty),
                Intro = GetDbString(reader["Intro"], string.Empty)
            };

            return user;
        }
    }
}
